/*
 *
 *  * Copyright 2020 byai.com All right reserved. This software is the
 *  * confidential and proprietary information of byai.com ("Confidential
 *  * Information"). You shall not disclose such Confidential Information and shall
 *  * use it only in accordance with the terms of the license agreement you entered
 *  * into with byai.com.
 *
 */

package com.wugui.datax.admin.util;

import cn.hutool.core.util.StrUtil;
import com.google.common.collect.Lists;
import com.wugui.datax.admin.exception.CustomException;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * create by xiuyuan 2019/11/25
 */
@Slf4j
public class ExcelUtils {

    private ExcelUtils() {
    }

    /**
     * 03文件
     *
     * @param filePath
     * @return
     */
    public static boolean isExcel2003(String filePath) {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    /**
     * 07文件
     *
     * @param filePath
     * @return
     */
    public static boolean isExcel2007(String filePath) {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }

    /**
     * 创建execl对象
     *
     * @param filePath
     * @return
     */
    public static Workbook getReadWorkBookType(InputStream inputStream, String filePath) {
        //xls-2003, xlsx-2007
        try {
            if (isExcel2007(filePath)) {
                return new XSSFWorkbook(inputStream);
            } else if (isExcel2003(filePath)) {
                return new HSSFWorkbook(inputStream);
            } else {
                throw new CustomException("excel格式文件错误");
            }
        } catch (IOException e) {
            throw new CustomException("excel创建失败");
        }
    }

    /**
     * 获取
     *
     * @param filePath
     * @return
     */
    public static Workbook getWriteWorkBoolType(String filePath) {
        if (filePath.toLowerCase().endsWith("xlsx")) {
            return new XSSFWorkbook();
        } else if (filePath.toLowerCase().endsWith("xls")) {
            return new HSSFWorkbook();
        } else {
            throw new CustomException("excel格式文件错误");
        }
    }

    /**
     * 读取execl
     *
     * @param inputStream
     * @param filePath
     * @param cellNumber
     * @return
     */
    public static List<List<String>> readExcel(InputStream inputStream, String filePath, Integer cellNumber, Integer maxColNum) {
        Workbook workbook = null;
        try {
            workbook = getReadWorkBookType(inputStream, filePath);
            List<List<String>> contents = Lists.newArrayList();
            // 遍历所有的sheet页
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                //获取第一个sheet
                Sheet sheet = workbook.getSheetAt(i);
                if (sheet.getPhysicalNumberOfRows() > maxColNum) {
                    log.info("导入Excel内容实际行数: {} 大于: {} 行", sheet.getPhysicalNumberOfRows(), maxColNum);
                    throw new CustomException("导入Excel内容大于" + maxColNum + "行");
                }
                //第0行是表名，忽略，从第二行开始读取
                for (int rowNum = 1; rowNum < sheet.getPhysicalNumberOfRows(); rowNum++) {
                    // 读取每一列数据
                    List<String> rowList = Lists.newArrayList();
                    Row row = sheet.getRow(rowNum);
                    for (int cellNum = 0; cellNum < cellNumber; cellNum++) {
                        Cell cell = row.getCell(cellNum);
                        if (cell == null) {
                            rowList.add("");
                        } else {
                            rowList.add(getCellStringVal(cell));
                        }
                    }
                    contents.add(rowList);
                }
            }
            return contents;
        } finally {
            if (inputStream != null) {
                IOUtils.closeQuietly(inputStream);
            }
            if (workbook != null) {
                IOUtils.closeQuietly(workbook);
            }
        }
    }

    /**
     * 写入文件
     *
     * @param targetFilePath
     * @param dataList
     * @return
     */
    public static Workbook writeExcel(String targetFilePath, String sheetName, List<List<String>> excelHeadList, Map<String, String> cellNameMap, List<Map<String, String>> dataList) {
        // 获取workbook
        Workbook workbook = getWriteWorkBoolType(targetFilePath);
        //创建sheet
        Sheet sheet = workbook.createSheet(sheetName);
        int rowNum = 0;
        if (CollectionUtils.isNotEmpty(excelHeadList)) {
            for (List<String> excelItem : excelHeadList) {
                Row titleRow = sheet.createRow(rowNum);
                for (int i = 0; i < excelItem.size(); i++) {
                    titleRow.createCell(i).setCellValue(StrUtil.nullToEmpty(excelItem.get(i)));
                }
                rowNum++;
            }
        }
        //在sheet第一行写出表单的各个字段名
        Row titleRow = sheet.createRow(rowNum);
        Map<String, Integer> cellMap = new HashMap<>();
        int cellNum = 0;
        for (String key : cellNameMap.keySet()) {
            Cell cell = titleRow.createCell(cellNum);
            cell.setCellValue(StrUtil.nullToEmpty(cellNameMap.get(key)));
            cellMap.put(key, cellNum);
            cellNum++;
        }
        rowNum++;
        // 遍历设置列值
        if (MapUtils.isNotEmpty(cellNameMap) && CollectionUtils.isNotEmpty(dataList)) {
            for (int i = 0; i < dataList.size(); i++) {
                Row dataRow = sheet.createRow(i + rowNum);
                Map<String, String> data = dataList.get(i);

                if (MapUtils.isNotEmpty(data)) {
                    for (String key : data.keySet()) {
                        Cell cell = dataRow.createCell(cellMap.get(key));
                        cell.setCellValue(StrUtil.nullToEmpty(data.get(key)));
                    }
                }
            }
        }
        return workbook;
    }

    /**
     * 获取具体的类型值
     *
     * @param cell
     * @return
     */
    public static String getCellStringVal(Cell cell) {
        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC:
                return cell.getNumericCellValue() + "";
            case HSSFCell.CELL_TYPE_STRING:
                return cell.getStringCellValue();
            case HSSFCell.CELL_TYPE_BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case HSSFCell.CELL_TYPE_FORMULA:
                return cell.getCellFormula();
            case HSSFCell.CELL_TYPE_BLANK:
                return "";
            case HSSFCell.CELL_TYPE_ERROR:
                return String.valueOf(cell.getErrorCellValue());
            default:
                return StringUtils.EMPTY;
        }
    }
}
